package jhta;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class CreateTable {
	String[] tableName = { "CODE", "BOOKMEMBER", "BOOK", "REQUEST", "RENT" };
	Connection conn;
	String sql;
	Statement stmt;
	ResultSet rs = null;

	public CreateTable() {
		try {
			conn = JavaDBConnection.getConn();
			
			
			//------------------------------------------
			// 없는 테이블 생성하기
			//------------------------------------------
			DatabaseMetaData dm = conn.getMetaData();

			for (int i = 0; i < tableName.length; i++) {
				rs = dm.getTables(null, null, tableName[i], null);

				if (!rs.next()) {
					switch (i) {
					case 0:
						createCode();
						break;
					case 1:
						createBookMember();
						break;
					case 2:
						createBook();
						break;
					case 3:
						createRequest();
						break;
					case 4:
						createRent();
						break;
					}
					
					Thread.sleep(100);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	private void createCode() throws Exception {
		sql = "CREATE TABLE BOOKRENT.CODE ("
				+ " CODE_NO          NUMBER NOT NULL,"
				+ " CODE_BIG         VARCHAR2(20) NOT NULL,"
				+ " CODE_SMALL       VARCHAR2(20),"
				+ " CODE_NAME        VARCHAR2(100)," 
				+ " CONSTRAINT BOOK_CODE"
				+ " PRIMARY KEY (CODE_NO) USING INDEX" + ")";
		
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		
		sql = "create sequence big_sequence";
		stmt.executeUpdate(sql);
		
		sql = "create sequence small_sequence";
		stmt.executeUpdate(sql);
		
		sql = "create sequence code_sequence";
		stmt.executeUpdate(sql);
		
		
		sql= "insert into code (code_no, code_big, code_name) "
				+ "values (code_sequence.nextval ,'A'||big_sequence.nextval , '도서') ";
		stmt.executeUpdate(sql);
		
		sql= "insert into code (code_no, code_big, code_name) "
				+ "values (code_sequence.nextval ,'A'||big_sequence.nextval , '관리자') ";
		stmt.executeUpdate(sql);
		
		sql= "insert into code (code_no, code_big, code_name) "
				+ "values (code_sequence.nextval ,'A'||big_sequence.nextval , '수강생') ";
		stmt.executeUpdate(sql);
		

	}

	private void createBook() throws Exception {
		sql = "CREATE TABLE BOOKRENT.BOOK("
				+ " NO               NUMBER NOT NULL,"
				+ " NAME             VARCHAR2(200),"
				+ " AUTHOR           VARCHAR2(200),"
				+ " PUBLISHER        VARCHAR2(200),"
				+ " PDATE            DATE," 
				+ " RDATE            DATE,"
				+ " TVOLUME          NUMBER NOT NULL,"
				+ " VOLUME           NUMBER NOT NULL,"
				+ " MEMBER_NO        NUMBER NOT NULL,"
				+ " CODE_NO          NUMBER NOT NULL," 
				+ " CONSTRAINT BOOK_PK"
				+ " PRIMARY KEY (NO) USING INDEX" + ")";
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		
		sql = "create sequence seq_book_no";
		stmt.executeUpdate(sql);
	

	}

	private void createBookMember() throws Exception {
		sql = " CREATE TABLE BOOKRENT.BOOKMEMBER( "
				+ " NO               NUMBER NOT NULL,"
				+ " NAME             VARCHAR2(20) NOT NULL,"
				+ " PHONE            VARCHAR2(20),"
				+ " PASSWORD         VARCHAR2(20)," 
				+ " RNO              NUMBER,"
				+ " RDATE            DATE DEFAULT SYSDATE,"
				+ " CODE_NO          NUMBER," 
				+ " CONSTRAINT MEM_MEMNO_PK"
				+ " PRIMARY KEY (NO) USING INDEX" + ")";
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		
		sql = "create sequence seq_mem_no";
		stmt.executeUpdate(sql);


	}



	private void createRequest() throws Exception {
		sql = "CREATE TABLE REQUEST(" 
				+ " SERIAL 			NUMBER PRIMARY KEY, "
				+ " MID 			NUMBER , " 
				+ " MDATE 			DATE DEFAULT SYSDATE, "
				+ " BOOKTITLE 		VARCHAR2(200)," 
				+ " BOOKPUBLISHER 	VARCHAR2(50),"
				+ " FLAG 			CHAR(1) DEFAULT 'q', " 
				+ " NOTE 			VARCHAR2(200),"
				+ " REPLNOTE 		VARCHAR2(200)," 
				+ " MANAGERID 		NUMBER,"
				+ " UDATE 			DATE DEFAULT SYSDATE, " 
				+ " GRP				NUMBER"
				+ ")";
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);

		sql = "create sequence request_seq";
		stmt.executeUpdate(sql);
	}

	private void createRent() throws Exception {
		sql = "CREATE TABLE BOOKRENT.RENT ( "
				+ " NO               NUMBER NOT NULL,"
				+ " MEMBER_NO        NUMBER NOT NULL,"
				+ " BOOK_NO          NUMBER NOT NULL,"
				+ " LDATE            DATE," 
				+ " WDATE            DATE,"
				+ " DDATE            DATE," 
				+ " EDATE            DATE,"
				+ " REG_NO           NUMBER NOT NULL,"
				+ " CONSTRAINT SYS_C007090"
				+ " PRIMARY KEY (NO) USING INDEX"
				+ " )";
		
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		
	}

}
